home *** CD-ROM | disk | FTP | other *** search
/ Personal Computer World 2008 February / PCWFEB08.iso / Software / Resources / Business / PaperCut Quota 6.3 / pc-setup(1).exe / {app} / WebAdmin / LogReport.asp < prev    next >
Encoding:
Text File  |  2005-10-13  |  23.9 KB  |  683 lines
  1. <%@ Language=VBScript %>
  2. <%' (c) Copyright 1999-2004 PaperCut Software Pty. Ltd. %>
  3. <!-- #include file="includes/PCCommon.inc" -->
  4. <!--#include file="includes/header.inc" -->
  5.  
  6. <%
  7. If Request("ReportList") <> "" Then
  8.     ' Display the report list (nothing else)
  9.  
  10.     Sub OutputReportRow(strReportKey, strRowClass, strQueryString)
  11.         Dim strURL
  12.         Dim strReportName
  13.         strReportName = GetText(strReportKey)
  14.  
  15.         strURL = "LogReport.asp"
  16.         If strQueryString <> "" Then
  17.             strURL = strURL & "?cmdRun=Y&blnShowParams=N&txtTitle=" & Server.URLEncode(strReportName) & "&" & strQueryString
  18.         End If
  19.     %>
  20.         <TR class="<%=strRowClass%>">
  21.             <TD><A href="<%=strURL%>"><%=strReportName%></A></TD>
  22.             <TD><%= GetText(strReportKey & "Desc")%></TD>
  23.         </TR>
  24.     <%
  25.     End Sub
  26.  
  27. %>
  28.  
  29.     <H1><%= GetText("QuickReports")%></H1>
  30.     <h2><%= GetText("PrintReports")%></h2>
  31.     <TABLE class="webAdmin">
  32.         <TR>
  33.             <TH><%= GetText("ReportName")%></TH>
  34.             <TH><%= GetText("Description")%></TH>
  35.         </TR>
  36.         <%
  37.             OutputReportRow "TopPrintUsersJobs", "evenRow", "cboUsageType=PrintJobs&cboReportType=SummaryByUser&cboPeriod=PeriodMonth&cboSortBy=PrintJobs"
  38.             OutputReportRow "TopPrintUsersPages", "oddRow", "cboUsageType=PrintJobs&cboReportType=SummaryByUser&cboPeriod=PeriodMonth&cboSortBy=PagesPrinted"
  39.  
  40.             OutputReportRow "BusiestPrintersJobs", "evenRow", "cboUsageType=PrintJobs&cboReportType=SummaryByPrinter&cboPeriod=PeriodMonth&cboSortBy=PrintJobs"
  41.             OutputReportRow "BusiestPrintersPages", "oddRow", "cboUsageType=PrintJobs&cboReportType=SummaryByPrinter&cboPeriod=PeriodMonth&cboSortBy=PagesPrinted"
  42.  
  43.             OutputReportRow "PrintJobsExpensive", "evenRow", "cboUsageType=PrintJobs&cboReportType=Detailed&cboPeriod=PeriodMonth&cboSortBy=Cost"
  44.             OutputReportRow "PrintJobsBiggest", "oddRow", "cboUsageType=PrintJobs&cboReportType=Detailed&cboPeriod=PeriodMonth&cboSortBy=PagesPrinted"
  45.  
  46.             OutputReportRow "CancelledAndNotRefundedJobs", "evenRow", "cboUsageType=PrintJobs&cboReportType=Detailed&cboPeriod=PeriodMonth&cboPrintStatus=CancelledWithNoRefund"
  47.  
  48.             OutputReportRow "AdhocReports", "oddRow", ""
  49.         %>
  50.     </TABLE>
  51.  
  52.     <% If gblnHasNetCharging Then %>
  53.     <h2><%= GetText("NetReports")%></h2>
  54.     <TABLE class="webAdmin">
  55.         <TR>
  56.             <TH><%= GetText("ReportName")%></TH>
  57.             <TH><%= GetText("Description")%></TH>
  58.         </TR>
  59.         <%
  60.             OutputReportRow "TopNetUsersData", "evenRow", "cboUsageType=NetUsage&cboReportType=SummaryByUser&cboPeriod=PeriodMonth&cboSortBy=NetDataUsed"
  61.             OutputReportRow "TopNetUsersTime", "oddRow", "cboUsageType=NetUsage&cboReportType=SummaryByUser&cboPeriod=PeriodMonth&cboSortBy=NetTimeUsed"
  62.  
  63.             OutputReportRow "NetHighestDailyUseData", "evenRow", "cboUsageType=NetUsage&cboReportType=Detailed&cboPeriod=PeriodMonth&cboSortBy=NetDataUsed"
  64.             OutputReportRow "NetHighestDailyUseTime", "oddRow", "cboUsageType=NetUsage&cboReportType=Detailed&cboPeriod=PeriodMonth&cboSortBy=NetTimeUsed"
  65.  
  66.             OutputReportRow "AdhocReports", "evenRow", ""
  67.         %>
  68.     <% End If %>
  69.     </TABLE>
  70.  
  71.     <!--#include file="includes/footer.inc" -->
  72. <%
  73.     Response.End
  74. End If
  75. %>
  76.  
  77.  
  78. <SCRIPT language="JavaScript">
  79. <!--
  80.     var iRepTypeSummaryUser = 0;
  81.     var iRepTypeSummaryPrinter = 1;
  82.     var iRepTypeDetailed = 2;
  83.  
  84.  
  85.     function changeUsageType() {
  86.         try {
  87.             var usage = document.getElementById("cboUsageType");
  88.             var printer = document.getElementById("cboPrinter");
  89.             var status = document.getElementById("cboPrintStatus");
  90.             var doc = document.getElementById("txtDocument");
  91.             var report = document.getElementById("cboReportType");
  92.             //alert(usage.selectedIndex);
  93.  
  94.             if (usage.selectedIndex == 0) {
  95.                 printer.disabled = false;
  96.                 status.disabled = false;
  97.                 doc.disabled = false;
  98.                 report.options[iRepTypeSummaryPrinter].disabled = false;
  99.  
  100.             } else {
  101.                 printer.disabled = true;
  102.                 status.disabled = true;
  103.                 doc.disabled = true;
  104.                 report.options[iRepTypeSummaryPrinter].disabled = true;
  105.             }
  106.         } catch (e) {
  107.             // do not error
  108.         }
  109.         return true;
  110.     }
  111.  
  112.  
  113.     function showParams(bShow) {
  114.         var d = document.getElementById("divReportParams");
  115.  
  116.         if (bShow) {
  117.             d.style.display = "";
  118.  
  119.         } else {
  120.             d.style.display = "none";
  121.  
  122.         }
  123.     }
  124.  
  125.     function submitSearch() {
  126.         var reportForm = document.getElementById("frmReport");
  127.         reportForm.submit();
  128.     }
  129.  
  130.     function submitSearchForRecord(intRec) {
  131.         var reportForm = document.getElementById("frmReport");
  132.         var startRecord = document.getElementById("inpStartRecord");
  133.         startRecord.value = intRec;
  134.         reportForm.submit();
  135.     }
  136.  
  137.     function exportToExcel() {
  138.         var reportForm = document.getElementById("frmReport");
  139.         var r = document.getElementById("cmdRun");
  140.         r.value = "ExportToExcel"
  141.         reportForm.submit();
  142.     }
  143.  
  144.     function toggleShowParams() {
  145.         var d = document.getElementById("divReportParams")
  146.         var link = document.getElementById("reportParamLink")
  147.         var inpShowParams = document.getElementById("inpShowParams")
  148.         if (d.style.display == "none") {
  149.             d.style.display = "";
  150.             inpShowParams.value = "Y";
  151.             link.innerHTML = "<A href=\"javascript:toggleShowParams();\"><%=GetText("HideReportParameters")%></A>";
  152.         } else {
  153.             d.style.display = "none";
  154.             inpShowParams.value = "N";
  155.             link.innerHTML = "<A href=\"javascript:toggleShowParams();\"><%=GetText("ShowReportParameters")%></A>   <A href=\"javascript:exportToExcel();\"><%=GetText("RunReportExcel")%></A>";
  156.         }
  157.     }
  158.  
  159.     function init() {
  160.         changeUsageType();
  161.  
  162.         var inpShowParams = document.getElementById("inpShowParams")
  163.         if (inpShowParams.value == "N") {
  164.             toggleShowParams();
  165.         }
  166.     }
  167.  
  168. -->
  169. </SCRIPT>
  170.  
  171. <%
  172.     Dim intMaxRows
  173.     Dim strReportTitle
  174.     intMaxRows = 50000
  175.     strReportTitle = GetText("AdhocReports")
  176.  
  177.  
  178.     Dim strUsageType
  179.     Dim strReportType
  180.     Dim strSortByCol
  181.     Dim strPeriod
  182.     Dim strStatus
  183.     Dim strPrinter
  184.     Dim strDocument
  185.     Dim strUsername
  186.  
  187.     strUsageType = LCase(Trim(Request("cboUsageType")))
  188.     strReportType = LCase(Trim(Request("cboReportType")))
  189.     strSortByCol = LCase(Trim(Request("cboSortBy")))
  190.     strPeriod = Trim(Request("cboPeriod"))
  191.     strStatus = Trim(Request("cboPrintStatus"))
  192.     strPrinter = Trim(Request("cboPrinter"))
  193.     strDocument = Trim(Request("txtDocument"))
  194.     strUsername = Trim(Request("txtUsername"))
  195.     If strStatus = "" Then
  196.         strStatus = "Printed"
  197.     End If
  198.  
  199.  
  200.     If Trim(Request("txtTitle")) <> "" Then
  201.         strReportTitle = Trim(Request("txtTitle")) & " (" & GetText(strPeriod) & ")"
  202.     ElseIf Request("cmdRun") <> "" Then
  203.         strReportTitle = GetText(Trim(Request("cboUsageType"))) & " - " & GetText(Trim(Request("cboReportType"))) & " (" & GetText(strPeriod) & ")"
  204.     End If
  205.  
  206.     Sub DisplayPrinterSelect(sName, strSelected)
  207.         Dim objSettings
  208.         Dim arrPrinters
  209.         Set objSettings = Server.CreateObject("PCWebAdmin.PCSettings")
  210.         arrPrinters = objSettings.GetPrinters()
  211.  
  212.         Set objSettings = Nothing
  213.  
  214.         Response.Write "<SELECT name=""" & sName & """ id=""" & sName & """>" & vbCRLF
  215.         Response.Write vbTab & "<OPTION></OPTION>" & vbCRLF
  216.  
  217.         Dim i
  218.         For i = Lbound(arrPrinters) to UBound(arrPrinters)
  219.             If strSelected = arrPrinters(i) Then
  220.                 Response.Write vbTab & "<OPTION SELECTED>" & Server.HTMLEncode(arrPrinters(i)) & "</OPTION>" & vbCRLF
  221.             Else
  222.                 Response.Write vbTab & "<OPTION>" & Server.HTMLEncode(arrPrinters(i)) & "</OPTION>" & vbCRLF
  223.             End If
  224.         Next
  225.         Response.Write "</SELECT>" & vbCRLF
  226.     End Sub
  227.     
  228.     Function SQLDateString(dtmDateTime)
  229.         ' Convert a date to a double.  We then have to replace commas with "." to make sure
  230.         ' we generate valid SQL even in locales where commas are used for the decimal place.
  231.         ' CStr() formats the number using locale settings and there doesn't appear to be the equivalent
  232.         ' of the Str() function which does not do locale based formatting
  233.         SQLDateString = Replace(CStr(CDbl(dtmDateTime)), ",", ".")
  234.     End Function
  235.     
  236.  
  237.     Sub DisplaySelect(sName, arrItems, strSelected, blnIncludeBlank, strExtraHTML)
  238.         Response.Write "<SELECT name=""" & sName & """ id=""" & sName & """ " & strExtraHTML & ">" & vbCRLF
  239.         If blnIncludeBlank Then
  240.             Response.Write vbTab & "<OPTION></OPTION>" & vbCRLF
  241.         End If
  242.  
  243.         Dim i, s
  244.         For i = 0 to UBound(arrItems)
  245.             s = arrItems(i)
  246.             If strSelected = s Then
  247.                 Response.Write vbTab & "<OPTION value=""" & s & """ SELECTED>" & GetText(s) & "</OPTION>" & vbCRLF
  248.             Else
  249.                 Response.Write vbTab & "<OPTION value=""" & s & """>" & GetText(s) & "</OPTION>" & vbCRLF
  250.             End If
  251.         Next
  252.         Response.Write "</SELECT>" & vbCRLF
  253.     End Sub
  254.  
  255.     Sub DisplayTextbox(sName, sValue)
  256.         Response.write "<INPUT name=""" & sName & """ id=""" & sName & """ type=""text"" value=""" & Server.HTMLEncode(sValue) & """ />"
  257.     End Sub
  258.  
  259.     Function EscapeSQLString(s)
  260.         EscapeSQLString = Replace(s, "'", "''")
  261.     End Function
  262.  
  263.     Function IgnoreField(s)
  264.         If s = "ID" Then
  265.             IgnoreField = True
  266.         ElseIf s = "Refunded" Then
  267.             IgnoreField = True
  268.         ElseIf s = "Cancelled" Then
  269.             IgnoreField = True
  270.         ElseIf s = "Denied" Then
  271.             IgnoreField = True
  272.         Else
  273.             IgnoreField = False
  274.         End If
  275.     End Function
  276.  
  277.  
  278.     Sub DisplayRecordSet(strURL, objRS, lngStartRecord)
  279.         Dim lngRow
  280.         Dim lngField
  281.         Dim strAlign
  282.         Dim strValue
  283.         Dim vntValue
  284.  
  285.         If not objRS.EOF Then
  286.             objRS.MoveLast
  287.             objRS.MoveFirst
  288.         End If
  289.  
  290.         If not objRS.EOF and not objRS.BOF Then
  291.             objRS.MoveFirst
  292.         End If
  293.  
  294.         If Not objRS.EOF and lngStartRecord > 1 Then
  295.             objRS.MoveFirst
  296.             objRS.Move lngStartRecord-1
  297.         End If
  298.  
  299.         Response.Write "<TABLE width=""100%"" border=""0""><TR><TD align=""right"">"
  300.         DisplayPageNavigation "javascript:submitSearchForRecord(%intStartRecord%);", lngStartRecord, objRS.RecordCount
  301.         Response.Write "</TD></TR></TABLE>"
  302.  
  303.  
  304.         Response.Write "<TABLE width=""100%"" class=""webAdmin"">"
  305.         ' Header
  306.         Response.Write "<TR>"
  307.         For lngField = 0 to objRS.Fields.Count - 1
  308.             If Not IgnoreField(objRS(lngField).Name) Then
  309.                 Response.Write "<TH>" & GetText(objRS(lngField).Name) & "</TH>"
  310.             End If
  311.         Next
  312.         Response.Write "</TR>" & vbCRLF
  313.  
  314.         If objRS.RecordCount = 0 Then
  315.             Response.Write "<TR><TD colspan=""" & objRS.Fields.Count & """>"
  316.             Response.Write GetText("ResultsNoData")
  317.             Response.Write "</TD></TR>" & vbCRLF
  318.         End If
  319.  
  320.         lngRow = 0
  321.         Do While Not objRS.EOF and lngRow < mintRECORDS_PER_PAGE
  322.             Response.Write "<TR class=""" & RowClassHelper(lngRow) & """>"
  323.             For lngField = 0 to objRS.Fields.Count - 1
  324.                 if Not IgnoreField(objRS(lngField).Name) Then
  325.                     vntValue = objRS(lngField).Value
  326.                     Select Case objRS.Fields(lngField).Type
  327.                         Case 10
  328.                             ' Str
  329.                             strAlign = "left"
  330.                             strValue = vntValue
  331.                         Case 7
  332.                             ' Double
  333.                             strAlign = "right"
  334.                             If IsNull(vntValue) Then
  335.                                 vntValue = 0
  336.                             End If
  337.                             strValue = FormatNumber(vntValue, 2, true, false, true)
  338.  
  339.                         Case 4
  340.                             ' Long
  341.                             strAlign = "right"
  342.                             If IsNull(vntValue) Then
  343.                                 vntValue = 0
  344.                             End If
  345.                             strValue = FormatNumber(vntValue, 0, true, false, true)
  346.  
  347.                         Case 5
  348.                             ' Currency
  349.                             strAlign = "right"
  350.                             strValue = FormatCredit(vntValue)
  351.  
  352.                         Case 8
  353.                             ' Date
  354.                             strAlign = "left"
  355.                             strValue = FormatDateTime(vntValue)
  356.                         Case Else
  357.                             strAlign = "left"
  358.                             strValue = vntValue & " - " & objRS.Fields(lngField).Type
  359.                     End Select
  360.                     Response.Write "<TD align=""" & strAlign & """>"
  361.                     Dim bHasShown
  362.                     bHasShown = False
  363.                     If objRS.Fields(lngField).Name = "Status" Then
  364.                         'Special formatting for print log status field
  365.                         On Error Resume Next
  366.                         If objRS("UserName") <> "" And objRS("TotalCost") > 0 _
  367.                                 And objRS("ID") > 0 And Not objRS("Refunded") Then
  368.                             If Err.Number = 0 Then
  369.                                 DisplayPrintJobStatus strValue, objRS("UserName"), objRS("TotalCost"), objRS("ID"), False, objRS("Cancelled"), objRS("Denied")
  370.                                 bHasShown = True
  371.                             End If
  372.                             Err.Clear
  373.                         End If
  374.                         On Error Goto 0
  375.                     End If
  376.                     If Not bHasShown Then
  377.                         Response.Write strValue
  378.                     End If
  379.                     Response.Write "</TD>"
  380.                 End If
  381.             Next
  382.             Response.Write "</TR>" & vbCRLF
  383.  
  384.             lngRow = lngRow + 1
  385.             objRS.MoveNext
  386.         Loop
  387.  
  388.         Response.Write "</TABLE>"
  389.         Response.Write "<TABLE width=""100%"" border=""0""><TR><TD align=""right"">"
  390.         DisplayPageNavigation "javascript:submitSearchForRecord(%intStartRecord%);", lngStartRecord, objRS.RecordCount
  391.         Response.Write "</TD></TR></TABLE>"
  392.  
  393.     End Sub
  394.  
  395. %>
  396. <H1><% = strReportTitle %></H1>
  397.  
  398. <p>
  399.     <A href="LogReport.asp?ReportList=Y"><%= GetText("QuickReports") %></A>   <SPAN id="reportParamLink"><A href="javascript:toggleShowParams();"><%= GetText("HideReportParameters") %></A></SPAN>
  400. </p>
  401. <DIV id="divReportParams">
  402.     <FORM ACTION="LogReport.asp" METHOD="POST" ID="frmReport">
  403.       <INPUT id="cmdRun" name="cmdRun" type="hidden" value="Y">
  404.       <INPUT id="inpShowParams" name="blnShowParams" type="hidden" value="<%=Request("blnShowParams")%>">
  405.       <INPUT id="inpStartRecord" name="intStartRecord" type="hidden" value="">
  406.       <TABLE class="webAdmin" width="500">
  407.         <TR class="<%=RowClassHelper(1)%>" >
  408.           <TH width="30%"><%=GetText("UsageType")%>:</TH>
  409.           <TD width="70%"><% 
  410. If gblnHasNetCharging Then
  411.     DisplaySelect "cboUsageType", Array("PrintJobs","NetUsage"), Request("cboUsageType"), False, " onchange=""changeUsageType();"" " 
  412. Else
  413.     DisplaySelect "cboUsageType", Array("PrintJobs"), Request("cboUsageType"), False, " onchange=""changeUsageType();"" " 
  414. End If
  415. %></TD>
  416.         </TR>
  417.         <TR class="<%=RowClassHelper(1)%>">
  418.           <TH><%=GetText("ReportType")%>:</Th>
  419.           <TD align="left"><% DisplaySelect "cboReportType", Array("SummaryByUser", "SummaryByPrinter", "Detailed"), Request("cboReportType"), False, "" %></TD>
  420.         </TR>
  421.         <TR class="<%=RowClassHelper(0)%>">
  422.           <TD colspan="2"><B><%=GetText("ReportFilters")%>:</B></TD>
  423.         </TR>
  424.         <TR class="<%=RowClassHelper(1)%>">
  425.           <TH><%=GetText("Period")%>:</Th>
  426.           <TD align="left"><% DisplaySelect "cboPeriod", Array("PeriodAll", "PeriodToday", "PeriodYesterday", "PeriodLast24Hours", "PeriodWeek", "PeriodMonth", "Period3Months", "Period6Months"), Request("cboPeriod"), False, "" %></TD>
  427.         </TR>
  428.         <TR class="<%=RowClassHelper(1)%>">
  429.           <TH><%=GetText("Username")%>:</Th>
  430.           <TD align="left"><% DisplayTextbox "txtUsername", Request("txtUsername") %></TD>
  431.         </TR>
  432.         <TR class="<%=RowClassHelper(1)%>">
  433.           <TH><%=GetText("Printer")%>:</Th>
  434.           <TD align="left"><% DisplayPrinterSelect "cboPrinter", Request("cboPrinter") %></TD>
  435.         </TR>
  436.         <TR class="<%=RowClassHelper(1)%>">
  437.           <TH><%=GetText("PrintStatus")%>:</Th>
  438.           <TD align="left"><% DisplaySelect "cboPrintStatus", Array("Printed", "Denied", "Cancelled", "Refunded", "CancelledWithNoRefund", "AllDocuments"), Request("cboPrintStatus"), False, "" %></TD>
  439.         </TR>
  440.         <TR class="<%=RowClassHelper(1)%>">
  441.           <TH><%=GetText("Document")%>:</Th>
  442.           <TD align="left"><% DisplayTextbox "txtDocument", Request("txtDocument") %></TD>
  443.         </TR>
  444.         <TR class="<%=RowClassHelper(0)%>">
  445.           <TD colspan="2"><B><%=GetText("ReportSortOrder")%>:</B></TD>
  446.         </TR>
  447.         <TR class="<%=RowClassHelper(1)%>">
  448.           <TH><%=GetText("SortBy")%>:</Th>
  449.           <TD align="left"><% 
  450. If gblnHasNetCharging Then
  451.     DisplaySelect "cboSortBy", Array("Time", "Username", "Printer", "PrintJobs", "PagesPrinted", "PagesPerJob", "Cost", "NetTimeUsed", "NetDataUsed"), Request("cboSortBy"), False, "" 
  452. Else
  453.     DisplaySelect "cboSortBy", Array("Time", "Username", "Printer", "PrintJobs", "PagesPrinted", "PagesPerJob", "Cost"), Request("cboSortBy"), False, "" 
  454. End If
  455. %></TD>
  456.         </TR>
  457.            <TR >
  458.           <TD colspan="2" align="right">
  459.               <INPUT type="submit" name="cmdRunButton" value="<%=GetText("RunReport")%>">
  460.               <INPUT type="button" onclick="exportToExcel();" value="<%=GetText("RunReportExcel")%>">
  461.           </TD>
  462.         </TR>
  463.       </TABLE>
  464.     </FORM>
  465. </DIV>
  466.  
  467. <%
  468. If Request("cmdRun") <> "" Then
  469.     Dim blnGroupByQuery
  470.     Dim strValidSorts
  471.     Dim strDateCol
  472.  
  473.     Dim strSQL
  474.     Dim strWhere
  475.     Dim strOrderBy
  476.  
  477.  
  478.     Dim strURL
  479.     strURL = "LogReport.asp?cboUsageType=" & Request("cboUsageType") & "&cboReportType=" & Request("cboReportType") & "&cboSortBy=" & Request("cboSortBy") & "&txtUsername=" & Request("txtUsername") & "&txtDocument=" & Request("txtDocument") & "&cboPrintStatus=" & Request("cboPrintStatus") & "&cboPrinter=" & Request("cboPrinter") & "&cboPeriod=" & Request("cboPeriod") & "&blnShowParams=" & Request("blnShowParams") & "&txtTitle=" & Request("txtTitle") & "&cmdRun=Go"
  480.  
  481.  
  482.     blnGroupByQuery = False
  483.     If strUsageType = "printjobs" Then
  484.         'Print Jobs
  485.         strDateCol = "[time]"
  486.         If strReportType = "detailed" Then
  487.             strSQL = "SELECT top " & intMaxRows & " [Time], Username, PrinterName as Printer, DocumentName as Document, pages as PagesPrinted, Cost as TotalCost, Status, ID, Refunded, Cancelled, Denied FROM JobLog WHERE 1 = 1 ##WHERE## ORDER BY ##ORDERBY## [time] desc, username asc"
  488.             strValidSorts = "time,username,printername,documentname,pages,cost,status"
  489.  
  490.         ElseIf strReportType = "summarybyuser" Then
  491.             strSQL = "SELECT top " & intMaxRows & " Username, CLng(SUM(pages)) as PagesPrinted, COUNT(*) as PrintJobs, AVG(pages) as PagesPerJob, SUM(cost) as TotalCost FROM JobLog WHERE 1 = 1 ##WHERE## GROUP BY username ORDER BY ##ORDERBY## username asc"
  492.             strValidSorts = "username,clng(sum(pages)),avg(pages),count(*),sum(cost)"
  493.             blnGroupByQuery = True
  494.  
  495.         ElseIf strReportType = "summarybyprinter" Then
  496.             strSQL = "SELECT top " & intMaxRows & " PrinterName, CLng(SUM(pages)) as PagesPrinted, COUNT(*) as PrintJobs, AVG(pages) as PagesPerJob, SUM(cost) as TotalCost FROM JobLog WHERE 1 = 1 ##WHERE## GROUP BY printername ORDER BY ##ORDERBY## printername asc"
  497.             strValidSorts = "printername,CLng(SUM(pages)),avg(pages),count(*),sum(cost)"
  498.             blnGroupByQuery = True
  499.  
  500.         End If
  501.     Else
  502.         'Net usage
  503.  
  504.         If Not gblnHasNetCharging Then
  505.             RedirectWithMessage "LogReport.asp", GetText("ReportsNoNetCharging")
  506.         End If
  507.  
  508.         strDateCol = "[logDate]"
  509.         If strReportType = "detailed" Then
  510.             strSQL = "SELECT top " & intMaxRows & " logdate as [Time], Username, totalmb as NetDataUsed, totalnethours as NetTimeUsed, Cost as TotalCost FROM NetChargeLog WHERE 1 = 1 ##WHERE## ORDER BY ##ORDERBY## logdate desc, username asc"
  511.             strValidSorts = "logdate,username,totalmb,totalnethours,cost"
  512.  
  513.             If strSortByCol = "time" Then
  514.                 strSortByCol = "logdate"
  515.             End If
  516.  
  517.         ElseIf strReportType = "summarybyuser" or strReportType = "summarybyprinter" Then
  518.             strSQL = "SELECT top " & intMaxRows & " Username, SUM(totalmb) as NetDataUsed, SUM(totalnethours) as NetTimeUsed, SUM(cost) as TotalCost FROM NetChargeLog WHERE 1 = 1 ##WHERE## GROUP BY username ORDER BY ##ORDERBY## username asc"
  519.             strValidSorts = "username,sum(totalmb),sum(totalnethours),sum(cost)"
  520.             blnGroupByQuery = True
  521.         End If
  522.  
  523.     End If
  524.  
  525.     If strUsername <> "" Then
  526.         strWhere = strWhere & " AND UserName LIKE '*" & EscapeSQLString(strUsername) & "*' "
  527.     End If
  528.  
  529.     If strUsageType = "printjobs" and strPrinter <> "" Then
  530.         strWhere = strWhere & " AND PrinterName = '" & EscapeSQLString(strPrinter) & "' "
  531.     End If
  532.  
  533.     If strUsageType = "printjobs" Then
  534.         If strStatus = "Cancelled" Then
  535.             strWhere = strWhere & " AND Cancelled = True "
  536.         ElseIf strStatus = "Refunded" Then
  537.             strWhere = strWhere & " AND Refunded = True "
  538.         ElseIf strStatus = "CancelledWithNoRefund" Then
  539.             strWhere = strWhere & " AND Cancelled = True AND Refunded = False "
  540.         ElseIf strStatus = "AllDocuments" Then
  541.             strWhere = strWhere & " "
  542.         Else
  543.             strWhere = strWhere & " AND Status LIKE '*" & strStatus &"*' "
  544.         End If
  545.     End If
  546.  
  547.     If strUsageType = "printjobs" and strDocument <> "" Then
  548.         strWhere = strWhere & " AND DocumentName LIKE '*" & EscapeSQLString(strDocument) & "*' "
  549.     End If
  550.  
  551.     If strPeriod <> "" Then
  552.         Dim dtmStartDate
  553.         Dim dtmEndDate
  554.         
  555.         ' A date in the future so we get all data by default.
  556.         dtmEndDate = DateAdd("D", +10, Date)
  557.  
  558.         Select Case strPeriod
  559.             Case "PeriodToday"
  560.                 dtmStartDate = Date
  561.             Case "PeriodYesterday"
  562.                 dtmStartDate = DateAdd("D", -1, Date)
  563.                 dtmEndDate = Date ' 12am this morning
  564.             Case "PeriodLast24Hours"
  565.                 ' Subtract a day from now.
  566.                 dtmStartDate = DateAdd("D", -1, Now)
  567.             Case "PeriodWeek"
  568.                 dtmStartDate = DateAdd("D", -7, Date)
  569.             Case "PeriodMonth"
  570.                 dtmStartDate = DateAdd("M", -1, Date)
  571.             Case "Period3Months"
  572.                 dtmStartDate = DateAdd("M", -3, Date)
  573.             Case "Period6Months"
  574.                 dtmStartDate = DateAdd("M", -6, Date)
  575.             Case "PeriodAll"
  576.                 ' no restriction
  577.         End Select
  578.  
  579.         If dtmStartDate <> 0 Then
  580.             strWhere = strWhere & " AND " & strDateCol & " >= " & SQLDateString(dtmStartDate) & " AND " & strDateCol & " <= " & SQLDateString(dtmEndDate) & " "
  581.         End If
  582.  
  583.     End If
  584.  
  585.     If strSortByCol <> "" Then
  586.         Dim strSortOrder
  587.  
  588.         Select Case strSortByCol
  589.             Case "username"
  590.                 strSortOrder = "ASC"
  591.             Case "printer"
  592.                 strSortOrder = "ASC"
  593.             Case Else
  594.                 strSortOrder = "DESC"
  595.         End Select
  596.  
  597.         If blnGroupByQuery Then
  598.             Select Case strSortByCol
  599.                 Case "cost"
  600.                     strSortByCol = "SUM(Cost)"
  601.                 Case "printjobs"
  602.                     strSortByCol = "COUNT(*)"
  603.                 Case "pagesprinted"
  604.                     strSortByCol = "clng(sum(pages))"
  605.                 Case "pagesperjob"
  606.                     strSortByCol = "avg(pages)"
  607.                 Case "days"
  608.                     strSortByCol = "COUNT(*)"
  609.                 Case "nettimeused"
  610.                     strSortByCol = "SUM(totalnethours)"
  611.                 Case "netdataused"
  612.                     strSortByCol = "SUM(totalmb)"
  613.                 Case "printer"
  614.                     strSortByCol = "printerName"
  615.             End Select
  616.         Else
  617.             Select Case strSortByCol
  618.                 Case "pagesprinted"
  619.                     strSortByCol = "pages"
  620.                 Case "nettimeused"
  621.                     strSortByCol = "totalnethours"
  622.                 Case "netdataused"
  623.                     strSortByCol = "totalmb"
  624.                 Case "printer"
  625.                     strSortByCol = "printerName"
  626.                 'Case Else
  627.                 '    strSortCol = "[" & strSortCol & "]"
  628.             End Select
  629.         End If
  630.  
  631.         'Response.write strValidSorts & "<BR>"
  632.         'Response.write strSortByCol & "<BR>"
  633.         If InStr(1, "," & strValidSorts & ",", "," & strSortByCol & ",", vbTextCompare) > 0 Then
  634.             strOrderBy = strOrderBy & strSortByCol & " " & strSortOrder & ", "
  635.         End if
  636.  
  637.     End If
  638.  
  639.  
  640.     strSQL = Replace(strSQL, "##WHERE##", strWhere)
  641.     strSQL = Replace(strSQL, "##ORDERBY##", strOrderBy)
  642.  
  643.     Response.Write "<!--" & strSQL & "-->"
  644.     'Response.End
  645.  
  646.     Dim objRS
  647.     Dim objReports
  648.     Set objReports = Server.CreateObject("PCWebAdmin.PCReports")
  649.     If strUsageType = "netusage" Then
  650.         Set objRS = objReports.QueryNetLog(strSQL)
  651.     Else
  652.         Set objRS = objReports.QueryPrintLog(strSQL)
  653.     End If
  654.     Set objReports = Nothing
  655.  
  656.     If Request("cmdRun") = "ExportToExcel" Then
  657.         ExportToExcel "UsageReport", objRS, True
  658.         Response.End
  659.     End If
  660.  
  661.     Dim intStartRecord
  662.     If Request("intStartRecord") = "" then
  663.         intStartRecord = 1
  664.     Else
  665.         intStartRecord = Int(Request("intStartRecord"))
  666.     End If
  667.  
  668.     DisplayRecordSet strURL, objRS, intStartRecord
  669.     objRS.Close
  670.     Set objRS = Nothing
  671. End If
  672. %>
  673.  
  674. <SCRIPT language="javascript">
  675. try {
  676.     init();
  677. } catch (e) {
  678.  
  679. }
  680. </SCRIPT>
  681.  
  682. <!--#include file="includes/footer.inc" -->
  683.